VLOOKUP multiple columns

wxman16 · May 12, 2017

Hi Folks,

I wanted to know what the formula would be when using a VLOOKUP formula for multiple columns from another sheet. This is the formula I have however, I’m getting an error message or that I have too many arguments.

Cell F6 = IF(F6=0,"",VLOOKUP($F6,'Sheet2'!$A$2:$F$9,COLUMNS('Sheet2’!$C$2:$C$9,columns('Sheet2’!$F$2:$F$9),0)

What’s I’m trying to do is, if cell F5 has a C.G # in there like 28.6, I want cell F6 to display the Trim number, which in this case based from the table below, cell F6 would be -0.1

Thanks for the help.
Brgds

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]row
/column[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]T/O
[/TD]
[TD="align: center"]C.G
[/TD]
[TD="align: center"]TRIM
[/TD]
[TD="align: center"]T/O
[/TD]
[TD="align: center"]CG
[/TD]
[TD="align: center"]TRIM
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]26.7[/TD]
[TD="align: center"]27.0[/TD]
[TD="align: center"]0.4[/TD]
[TD="align: center"]34.0[/TD]
[TD="align: center"]34.2[/TD]
[TD="align: center"]-1.6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]27.1[/TD]
[TD="align: center"]27.4[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]34.3[/TD]
[TD="align: center"]34.6[/TD]
[TD="align: center"]-1.7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]27.5[/TD]
[TD="align: center"]27.7[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]34.7[/TD]
[TD="align: center"]35.0[/TD]
[TD="align: center"]-1.8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]27.8[/TD]
[TD="align: center"]28.1[/TD]
[TD="align: center"]0.1[/TD]
[TD="align: center"]35.1[/TD]
[TD="align: center"]35.3[/TD]
[TD="align: center"]-1.9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]28.2[/TD]
[TD="align: center"]28.4[/TD]
[TD="align: center"]0.0[/TD]
[TD="align: center"]35.4[/TD]
[TD="align: center"]35.7[/TD]
[TD="align: center"]-2.0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]28.5[/TD]
[TD="align: center"]28.8[/TD]
[TD="align: center"]-0.1[/TD]
[TD="align: center"]35.8[/TD]
[TD="align: center"]36.1[/TD]
[TD="align: center"]-2.1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]28.9[/TD]
[TD="align: center"]29.2[/TD]
[TD="align: center"]-0.2[/TD]
[TD="align: center"]36.2[/TD]
[TD="align: center"]36.4[/TD]
[TD="align: center"]-2.2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]29.3[/TD]
[TD="align: center"]29.5[/TD]
[TD="align: center"]-0.3[/TD]
[TD="align: center"]36.5[/TD]
[TD="align: center"]36.8[/TD]
[TD="align: center"]-2.3[/TD]
[/TR]
</tbody>[/TABLE]

Nine Zero · May 12, 2017

Hey,

Either I am reading it wrong or perhaps your cells and numbers are all messed up according to your explanation

Aladin Akyurek · May 12, 2017

@wxman16

Try to describe in words (not in terms of a formula) the objective you have in mind with respect to the sample you posted.

FDibbins · May 12, 2017

You have shown where you are pulling info from, now can you show what you are working with, that will reference that table?

dUBBINS · May 12, 2017

Error. Just to address your error alone. You have too many arguments, because the syntax is:

IF(TEST for TRUE or FALSE, DO if TRUE, DO if FALSE)

The break down of your formula:

You are TESTING to see if F6=0:
= IF(F6=0,

DO if TRUE, you want it to be blank if F6=0:
"",

DO if FALSE, you want to use a VLOOKUP() if F6 does not =6:
VLOOKUP($F6,'Sheet2'!$A$2:$F$9,COLUMNS('Sheet2’!$C$2:$C$9,columns('Sheet2’!$F$2:$F$9)

This part is why you have an error. If you remove the comma and the zero it should go away. And if you want it to be zero instead of blank change it in the DO if TRUE section above. Instead of ,"", simply make it ,0,
,0)

wxman16 · May 12, 2017

Nine Zero said:
Either I am reading it wrong or perhaps your cells and numbers are all messed up according to your explanation

No, you are not reading wrong. The table is correct. What I should have said is cell F12 not F6, should be -0.1
So the question should is, in cell F12, if cell F11 has a number like 29.1, what VLOOKUP formula should be used when to display the number from column C and F when it is looking up 29.1?

Sorry about that confusion.

Aladin Akyurek · May 13, 2017

wxman16 said:
No, you are not reading wrong. The table is correct. What I should have said is cell F12 not F6, should be -0.1
So the question should is, in cell F12, if cell F11 has a number like 29.1, what VLOOKUP formula should be used when to display the number from column C and F when it is looking up 29.1?

Sorry about that confusion.

The exhibit you posted shows a table which runs from A1 to F9, the headers included. There is no F12 or F11 up there.

Care to clarify what you are looking for in this table clearly and concisely?

VLOOKUP multiple columns

wxman16

New Member

Excel Facts

Nine Zero

Well-known Member

Aladin Akyurek

MrExcel MVP

FDibbins

Well-known Member

dUBBINS

Well-known Member

wxman16

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

VLOOKUP multiple columns

wxman16

New Member

Excel Facts

Nine Zero

Well-known Member

Aladin Akyurek

MrExcel MVP

FDibbins

Well-known Member

dUBBINS

Well-known Member

wxman16

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock