I want to lookup the amount using an Excel function. It seems VLOOKUP is not possible as the database has multiple columns. I tried searching online,but in vain as I am unable to put my issue in Google search.
Can someone please suggest an Excel formula which can be used in such a scenario?
No problem; VLOOKUP() can handle this.
In your example screen shot, I'm assuming that "Base Data" is in cell A1. If so, then the following formula can be put in cell D13, which is first cell in which you have "??":
=VLOOKUP(A14,$A$3:$E$8,VALUE(SUBSTITUTE(B14,"Location ",""))+1,FALSE)
Here's how those arguments down:
A14 is the cell you're trying to match; in this case it is "102".$A$3:$E$8 is the block you're trying to match it in; you need the dollar signs to make it static as you copy the formula down to other cells.VALUE(SUBSTITUTE(B14,"Location ",""))+1 turns cell B14 from "Location 2" to the digit 2, and then adds 1 to it (it is necessary to add 1 because column 1 will be the Account Code column, column 2 is Location 1, column 3 is Location 2, etc.).FALSE forces exact matches.Joe DeRose's solution makes clever use of the SUBSTITUTE function to take advantage of the labels in your example. If you used those labels as a generic example and they are really something else, SUBSTITUTE won't help in that case. A more generalized approach that will work on any labels would be to use the MATCH function (the remainder of the formula would remain the same). It's easy to get cross-eyed identifying cells in examples. My calculation is that the table is in A3:G8 and the first lookup example is in A13:C13, so I'll use that in the formula below:
=VLOOKUP(A13,$A$3:$G$8,MATCH(B13,$A$2:$G$2,0),FALSE)
MATCH returns the position of the value in B13 in the heading row, which is the number of the column to use for the VLOOKUP selection. The zero serves the same function as the FALSE in VLOOKUP (do an exact match). As with Joe DeRose's answer, the cell references are locked where necessary (the $s), so you can enter the formula in C13 and then copy it down the column as needed.
If your lookup list will be expanding down the page, you can get fancy and pre-populate more cells in column C than you need and hide them until they are used. Add a test using the ISBLANK function:
=IF(ISBLANK(A13,"",VLOOKUP(A13,$A$3:$G$8,MATCH(B13,$A$2:$G$2,0),FALSE))
This checks to see if A13 is empty and returns a null (blank cell) if it is. Otherwise it uses the formula above. You can copy this down column C for an arbitrarily large range of cells. The cells will remain blank until you enter lookup values.
