0

Im trying to create a dropdown list on a sheet, that looks up a matched value in another sheet. In the picture below is of a sheet called PricesforCSV. Here I want the circled value in column B to be value pulled from a second sheet called invoiceLookup:

Defined List in action Column B should list the price, but is currently 'FALSE'

As you can see, so far I have the dropdown using working using data validation. It is defined as so: =invoiceLookup!$D:$D,

The sheet invoiceLookup which as the information I want to look up is below. Column E has the pricing I want to bring across.

This sheet holds the information I want to lookup

In the circled cell in Column B of PricesforCSV I am trying to perform the lookup based on the value of the dropdown. Here is the formula Im using so far for this cell:

 =IF(ISNA(VLOOKUP(A3,invoiceLookup!D2:D300,2,0)),
  VLOOKUP(A3,invoiceLookup!E2:E300,2,0))

This is currently returning false.

The idea here is to search all of the rows in invoiceLookup!D2:D300 looking for a match to the value in A3, and if it is found, populate the cell with the related value in the next column: invoiceLookup!E2:E300.

  • Im not sure this is the best approach, could I be using a two column named list, and get the values from this?
  • There may be an issue with whitespace in the items from the dropdown list, though trying values that have no whitespace currently doesn't solve the problem.

Thanks for taking the time to look at this!

1 Answer 1

0

As in many cases simply taking the time to ask the question properly, and with fresh eyes simplifying the formula until I started to see results led to the answer.

This is what I arrived at:

=IF(NOT(ISNA(VLOOKUP(A3,invoiceLookup!$D:$E,2,0))),VLOOKUP(A3,invoiceLookup!$D:$E,2,0))
  • First problems was in the VLOOKUP, the second value in the array is where the return value is stored, I thought it asked for a beginning and ending point for the lookup. In effect I was looking for the return value in the same column as the lookup value. The corrected version now looks into the whole column for lookup $D and the whole column for return value $E: VLOOKUP(A3,invoiceLookup!$D:$E,2,0)

  • I also misunderstood ISNA which returns true when there is an empty cell (I thought opposite) this led me to adding NOT to the first half of the formula: =IF(NOT(ISNA(VLOOKUP(A3,invoiceLookup!$D:$E,2,0))), ...

EDIT ----------------------- A slight revision, as the old version added "FALSE" to cells with an empty value. The following leaves the cell empty:

=IF(ISNA(VLOOKUP(A20,invoiceLookup!$D:$E,2,FALSE)),"",VLOOKUP(A20,invoiceLookup!$D:$E,2,FALSE))

/EDIT ----------------------

I realise that Excel formulas don't get that much traffic, as only 19 or so have looked at this in the past day, but for those that run into this again, I hope it proves useful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.