Need help on Vlookups

[Brian13]

Hello,

I am going to try and describe my issue in as much detail as possible, I hope someone out here can be of assistance.

I have a spread sheet that has a small table that contains two columns. The first is the name of a company, the second being their respective growth in sales this year/ last year. They are in no particular order.

Anyways, that data has to be transplanted to a larger table, which contains the company name, that financial metic as well as a host of others.

I am to rank them in this large chart based off of the year sales/sales metric. I started by using LARGEST function to rank the data on the big table. I managed that successfully.

I am now trying to use VLookup to populate the company name part, based on the sorted data from the LARGEST function.

I keep returning errors.

My Formula in cell B5 looks as such =VLOOKUP(D5, H35:J62, 2)

D5 being the first cell in the big table for the sales/sales metric, that already contains the LARGEST function.

H35:J62 is the smaller table where the names and values for the larger table are located

2, is the column that contains the actual numeric data in the smaller table. The smaller table is laid out "Company Name X", "X company's Sales/Sales Value"

I did not use the TRUE/FALSE argument as leaving it blank=writing TRUE, but I have also tried false.

I keep getting "N/A" as a result.

Again, I am trying to display the name of the company that is tied to a specific metric in the lower table. If VLOOKUP is not what I need to be using then please, let me know.

Thanks for the help.

[ChemistB]

It sounds like you are trying to find column A of the smaller table from column B (Or H using I, not sure where J is coming into this since you say the table only has two columns)

VLOOKUP's only work left to right. You might try using INDEX/MATCH

=INDEX($H$35:$H$63, MATCH(D5, $I$35:$I$62,0))
An example spreadsheet would help tremendously (Go Advanced>Manage Attachments)
Also, FYI, since you say the small table is in no particular order, you would need to use the FALSE argument in a VLOOKUP to get an exact match.

[ben_hensel]

B5 is looking in column H35:H62 for the value that exists in cell D5 and getting a row index; and then returning whatever is in I35:I62 at the same index position.

To be clear, that's what your formula is actually doing, so if that's not correct, then you've got a logical design problem.

#N/A is thrown when there's no matching value. If they're spelled differently (eg, "ACME co" vs "ACME") than that's not the same for VLOOKUP. Make sure that's not your problem.

Anyway, without seeing a posted example spreadsheet, I'd hazard to guess that your ranges are marching down too, so try this syntax with objective range references:

Please Login or Register  to view this content.

If that doesn't fix it, please post an example spreadsheet so we can diagnose what it's doing.

[Brian13]

Here is the sample file.

Thanks

[ben_hensel]

Ah, you've heretically merged cells inside a table, so lookups on the I53:I62 range is returning a bunch of nonsense (well, all zeros, to be precise).

You want to be looking up on column J and returning on column H, so ChemistB's INDEX(MATCH) suggestion is the best way forward, although the syntax will have to be changed slightly:

Please Login or Register  to view this content.

in B5 and pull down.

[Brian13]

You are a life saver. Thank you, tremendously.

If I may ask one more question.... When I have the sales/sales data in the Top/Big Table and I get #NUM! because the formula isn't valid anymore (Some companies don't have prev year data) How can I change those to just say "n/a"?

Also, that Index formula fails when you get to them as well, anything that can be done on that front?

[Brian13]

One last question, then I am done. I promise.

On the column between the two that we just generated. The gross sales column, is there a way to make excel look at the company name in the first row and populate accordingly using the data in the bottom left table.

In other words, it would look in B5-B32, Match it to it's corresponding cell in b35-b62 and pull up that corresponding value in c35-c62?

[ben_hensel]

1) Replace the formula in D5 with this:

Please Login or Register  to view this content.

That will return "n/a" as a text string if the LARGE returns any error. (Also changed the 25, 24, etc to a relative reference based on the ROW so you can pull down instead of changing them manually).

2)

Also, that Index formula fails when you get to them as well, anything that can be done on that front?

Yeah, what do you want it to do? Crash out and do nothing? That's easy, just wrap in IF like this:
=IF(D5 = "n/a", "", do_the_lookup)
or something.

Keep adding companies even though you've got the exact same indexing value for your lookup? That's much harder.

3)

Please Login or Register  to view this content.

like that?