VLOOKUP: if match found insert cooresponding data in the same row, different column

[lacey026]

This is my first post...an ANY forum. I am also pretty new to formulas other than your basics. Here's my problem:

I used vlookup (with help) to find out if the vendor part number I have for a part matched the actual vendor part number for the same part. If it matched, it inserted that vendor part number.

Instead of it inserting the vendor part number when it is matched, I need a it to be replaced with my part number for that part. That data will come from the same row, different column that the vendor part number was located on.

I hope this makes sense. TIA!

 

[lacey026]

I don't know if I should add that my part number is located on a different sheet in the same workbook than the formula is located.

 

[AliGW]

OK, so what is the VLOOKUP formula you are using at the moment? You just need to tweak it:

=VLOOKUP(lookup_value,lookup_array,column,false)

lookup_value - no change
lookup_array - needs to encompass the column where the data you wish to return is, so may need to be wider
column - needs to change to the number of the column within the lookup_array where the new return value is located, e.g. in the array $A$1:$D$8, A is column 1, B is 2, C is 3 and D is 4.

 

[lacey026]

OK, so what is the VLOOKUP formula you are using at the moment? You just need to tweak it:

=VLOOKUP(lookup_value,lookup_array,column,false)

lookup_value - no change
lookup_array - needs to encompass the column where the data you wish to return is, so may need to be wider
column - needs to change to the number of the column within the lookup_array where the new return value is located, e.g. in the array $A$1:$D$8, A is column 1, B is 2, C is 3 and D is 4.

This is the formula I'm using: =VLOOKUP(TEXT(G57,0),'sheet 5'!J:J,1,FALSE)

"Text" is there due to formatting, per my helper.

The "G57,0" is the data I need to match, the next section is on a different sheet referencing the ACTUAL data (which is only 1 column of data). Then if it matches, it needs to be replaced with data from the same row, different column as the ACTUAL data.

 

[AliGW]

OK, so the column you want to reference is before the one already in the VLOOKUP, so you are going to need to use an INDEX MATCH query instead. Without seeing the actual data, it's hard to give you exactly what you need, though.

 

[lacey026]

I think I understand what you mean. Instead of referencing the 1 column on sheet 5, I need to reference both the vendor part # data AND my part number data, then the column # needs to reference which column I want it replaced with. Yes? Do the columns have to be next to each other?

 

[AliGW]

The beauty of INDEX MATCH is that the columns don't have to be next to each other. It would be something like this:

=INDEX($B$2:$B12,MATCH(A1,$G$2:$G$12,0),0)

where the B range is the column of data where the return value is, A1 is the lookup value and the G range is the lookup range.