CODEWITHSUNDEEP
excel
Alfonsus Dhani
Alfonsus Dhani

Reputation: 105

Using IF Function Range in Excel

I have some problem in using If Excel function. I want to use range in excel. So when my data is in range, it will show something. I use this formula but it doesn't work "

=IF(E11=Rekap.C8:C21, VLOOKUP(Rekap.C8:C21, Master.A2:C148, 3), "")

Form that formula, if data in E11 is same like data in range C8 - C21 in Rekap sheet, it will show another data in Master sheet that in range A2-C148 column 3. How can I use range in if formula ?

Upvotes: 0

Views: 631

Answers (1)

teylyn
teylyn

Reputation: 35915

A single cell like E11 cannot be compared to a range like C8:C21.

I assume that you really want to check if the value in E11 appears anywhere in the range C8:C21 and if so, perform the lookup. That can be done in several ways

=if(isnumber(match(e11,Rekap!$C$8:$C$21,0)),vlookup(E11,Master!$A$2:$C$148,false),"")

Note that I added the fourth parameter to the Vlookup. It will default to TRUE if omitted, which may return wrong results if the lookup table is not sorted.

Upvotes: 1

Related Questions