0

I need some help for a lookup formula in excel sheet. I am basically trying to find out a corresponding value of for a row, where two conditions in the same row meet. Let me illustrate it his way. If within the range of column A a certain value is found and in the same row but in Column C other certain value is equal then return the value in Column B for the same row.

The table is as follows:

Column A      Column B                        Column C 
---------------------------------------------------------
Ahmadaba      Irrigation                      550,041 
Ahmadaba      Livelihood                      18,890 
Ahmadaba      Power                           233,694 
Ahmadaba      Public Building                 64,707 
Ahmadaba      Rural Development               104,381 
Ahmadaba      Transport                       1,044,644 
Ahmadaba      Water Supply & Sanitation       1,156,322

To elaborate; in reference to above table if Column A is equal to Ahmadaba and Column C is equal to 18,890 then I should get the value Livelihood from column B.

lc.
  • 113,939
  • 20
  • 158
  • 187
Arafat Safi
  • 1

1 Answers1

1

By far the least resource-heavy (and therefore most efficient) set-up would be to use one additional column within your table in which to generate a series of unique identifiers based on the entries in columns A and C.

So if your table is currently in, for example, A1:C8 (with headers in row 1), then you could use column D to this end, entering this formula in D2:

=A2&"|"&C2

(The separator is probably not strictly necessary in your case, though in general it is good practice.)

and copied down to D8.

After which, and assuming your chosen search values, e.g. "Ahmadaba" and "18,890" are in E1 and F1 respectively, the required formula is:

=INDEX(B2:B8,MATCH(E1&"|"&F1,D2:D8,0))

Note that one of the advantages of this set-up is that you can also employ a more flexible variation of the above which references not just 8 rows but an entire column's worth, viz:

=INDEX(B:B,MATCH(E1&"|"&F1,D:D,0))

with virtually no detriment to calculation performance.

If, for whatever reason, you cannot use an additional column within your table to perform these concatenations, then an alternative solution would be:

=LOOKUP(1,0/((A2:A8=E1)*(C2:C8=F1)),B2:B8)

However, it should be pointed out that, with this set-up, the amount of calculation Excel has to perform is directly proportional to the size of the range being referenced. As such, unlike with the INDEX/MATCH construction, it would be a disastrous idea here to use:

=LOOKUP(1,0/((A:A=E1)*(C:C=F1)),B:B)

which is forcing Excel to calculate more than 2 million cells.

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15