How to apply a vlookup formula in a indirect formula?

Acsadac

New Member
Joined
Jun 11, 2015
Messages
2
Hi all,

I am currently doing a project at work, where I need som help:

Task: I have a data sheet, where I have a lot of unique keys, which I am to search for in an output sheet.

In the output-sheet the unique key that I am to search for is derived from two drop-down lists and one cell with static information. My problem is, that the information from the drop-down list is not applicable for the look-up in the data sheet. This means that I need to do a vlook-up into to find the text that correspondes to the text in the uniqe cells.

Could any one of you tell me, how I do a combination of a indirect formula and vlookup?

I have tried the following: =INDIRECT(A24;VLOOKUP("text";'DROP-DOWN LIST'!G3:H11;FALSE))

Thank you in advance!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

AliMass

New Member
Joined
Jun 4, 2015
Messages
14
I don't follow your description. As a matter of principle, you can combine INDIRECT and VLOOKUP (although not quite the way you put it). Can you show an example of your data and how you want the result?
 
Last edited:

AliMass

New Member
Joined
Jun 4, 2015
Messages
14
I see your screenshot, but ignored your massive formula. In Revenue and Sales Contributions (cells B10:C13), you want to bring in what is in columns J:K, correct?

I see that the cells A10:A13 would then connect to A3:B6 to know the two-character country code, and then the country code would link to, I assume, column H to get the right Revenue and Sales Contribution. Then you have a second parameter, which is D3:E6. In your example, it seems to match to A9.

One item I don't get is that in columns H and I, the items are not unique. I see DK and S combination in rows 3, 7, 11, and 15. Perhaps you meant to have the four countries with Shoes, and then the four countries with Jeans, etc.?

Assuming the items in columns H and I are unique (so column G is unique), put into B10 this formula (I haven't tested it, though, and if outside the US, you may need to change the comma to a semicolon): =VLOOKUP(VLOOKUP($A10,$A$3:$B$6,2,FALSE)&VLOOKUP($A$9,$D$3:$E$6,2,FALSE),$G$3:$K$18,4,FALSE)

Basically, I looked up the country code and added the clothing code. This put together matches column G, which the overall VLOOKUP will then grab the fourth column (column J). This should work if you copy to column C, but change the 4 toward the end to a 5.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top